package cn.itlaobing.spring.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ArgumentPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import cn.itlaobing.spring.dao.BaseDao;
import cn.itlaobing.spring.dao.StudentDao;
import cn.itlaobing.spring.entity.Student;

@Repository
public class StudentDaoImpl extends BaseDao implements StudentDao {

	@Override
	public void addStudent(Student student) {
		// 用来获取自动增长值
		KeyHolder keyHolder = new GeneratedKeyHolder();

		String sql = "INSERT INTO students(NAME,gender,birthday) VALUES (?,?,?)";

		Object[] params = new Object[] { student.getName(), student.getGender().name(), student.getBirthday() };

		// return
		// super.getJdbcTemplate().update(sql,student.getName(),student.getGender().name(),student.getBirthday());
		super.getJdbcTemplate().update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement pstat = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
				PreparedStatementSetter pss = new ArgumentPreparedStatementSetter(params);
				pss.setValues(pstat);
				return pstat;
			}
		}, keyHolder);
		// 获取返回的自增长id
		student.setId(keyHolder.getKey().intValue());
	}

	@Override
	public Student findById(Long id) {
		try {
			String sql = "select * from students where id=?";
			return super.getJdbcTemplate().queryForObject(sql, new BeanPropertyRowMapper<>(Student.class), id);
		} catch (DataAccessException e) {
			return null;
		}
	}

	@Override
	public int deleteID(Long id) {
		String sql = "DELETE FROM students WHERE id=?";
		return super.getJdbcTemplate().update(sql, id);
	}

	@Override
	public int update(Student student) {
		String sql = "UPDATE students SET NAME=?,gender=?,birthday=? WHERE id=?";
		return super.getJdbcTemplate().update(sql, student.getName(), student.getGender().name(), student.getBirthday(),
				student.getId());
	}

	@Override
	public List<Student> findAll() {
		String sql = "select id,name,gender,birthday from students";
		return super.getJdbcTemplate().query(sql, new BeanPropertyRowMapper<>(Student.class));
	}

}
